<?php

session_start();
include 'conn.php';
$ActivityId = $_SESSION['selectedActivity'];
if (!empty($_SESSION['centreName'])) {
    include 'conn.php';
    $qry = "Select CentreId from Centre where CentreName='" . $_SESSION['centreName'] . "'";
    $result = mysql_query($qry);
    while ($row = mysql_fetch_array($result)) {
        $centreId = intval($row['CentreId']);
    }
    $cname = $_SESSION['centreName'];
} else {
    include 'conn.php';
    $centreId = $_SESSION["centreId"];
    $qry = "Select CentreName from Centre where CentreId=$centreId";
    $result = mysql_query($qry);
    if ($result) {
        while ($row = mysql_fetch_array($result)) {
            $cname = $row['CentreName'];
            $_SESSION["cname"] = $cname;
        }
    }
}
$actsql = mysql_query("Select ActivityName from activity where ActivityId='$ActivityId'");

while ($row = mysql_fetch_array($actsql)) {
    $ActivityName = $row['ActivityName'];
}
$startDate = $_SESSION["startDate"];
$endDate = $_SESSION["endDate"];
if ($centreId == 0) {
    $clwhere = "client.CentreId is not null";
//echo "testing ";
    $where = "CentreId is not null";
    $kwhere = "INNER JOIN Centre on client.CentreId=centre.CentreId where client.CentreId is not null";
} else {
    $clwhere = "client.CentreId=$centreId";
    $where = "CentreId=$centreId";
    $kwhere = "INNER JOIN Centre on client.CentreId=centre.CentreId where client.CentreId=$centreId";
//echo "testing2";
}


require_once '../Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$startDate1 = date('dmy', strtotime($startDate));
$endDate1 = date('dmy', strtotime($endDate));
//$objConditional1 = new PHPExcel_Style_Conditional();
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->getStyle('A3:Z3')->getFont()->setBold(true);
$objPHPExcel->getProperties()->setTitle("EldercareActivityReport" . $startDate . "to" . $endDate);
if ($centreId == 0) {
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Activity Report from ' . $startDate1 . ' to ' . $endDate1);
} else {
    include 'conn.php';
    $check1 = "Select CentreName from Centre where CentreId=$centreId";
    $resultto = mysql_query($check1);
    while ($row = mysql_fetch_array($resultto)) {
        $cname = $row['CentreName'];
    }
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Activity Report from ' . $startDate1 . ' to ' . $endDate1 . ' for ' . $cname);
}

$objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
$objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


include 'conn.php';
$checkDateSNo = $startDate;
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
$objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
$objPHPExcel->getActiveSheet()->SetCellValue('A3', 'S/No');
$objPHPExcel->getActiveSheet()->SetCellValue('B3', 'NRIC');
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
$objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
$column = 'E';
$userType = $_SESSION["userType"];
if ($userType == 'System Admin') {
    $objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Centre Name');

    $objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(25);
    $column++;
}
$objPHPExcel->getActiveSheet()->SetCellValue($column . '3', 'Age');
$objPHPExcel->getActiveSheet()->getStyle($column . '3')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
$column++;

$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(13);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(13);
$rowCount = 3;
$count = 1;
while ($checkDateSNo <= $endDate) {
    $sqlhols = "Select count(*) from holiday where Date='$checkDateSNo'";
    $resulthols = mysql_query($sqlhols);

    while ($row = mysql_fetch_array($resulthols)) {
        if ($row['count(*)'] == 0) {
            $day = date('l', strtotime($checkDateSNo));
            if ($day != "Sunday" && $day != "Saturday") {
                $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $count);
                $objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                $count++;
                $column++;
            } else {
                $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
            }
        } else {
            $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
        }
    }
}
$objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Average Performance');
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getFont()->setBold(true);
$column++;
$objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Total Attendance');
$objPHPExcel->getActiveSheet()->getColumnDimension($column)->setWidth(13);
$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getFont()->setBold(true);
$rowCount++;

$ttlattendance = 0;
include 'conn.php';
$sql = "SELECT * from Client $kwhere";
$result = mysql_query($sql);
$sno = 1;
while ($row = mysql_fetch_array($result)) {
    $objPHPExcel->getActiveSheet()->mergeCells('C' . $rowCount . ':D' . $rowCount);
    $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $sno);
    $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['NRIC']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $row['CustomerName']);
    $column = 'E';
    if ($userType == 'System Admin') {
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $row['CentreName']);
        //$objPHPExcel->getActiveSheet()->getStyle($column . $rowCount)->getAlignment()->setWrapText(true);
        $column++;
    }
    $Age = date('Y-m-d') - $row['DateOfBirth'];
    $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $Age);
    $column++;

    $checkDate = $startDate;
    $workingday = 0;
    $attendance = 0;
    $totalPerformance = 0;
    while ($checkDate <= $endDate) {
        $sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
        $resulthols1 = mysql_query($sqlhols1);

        while ($rowhol = mysql_fetch_array($resulthols1)) {
            if ($rowhol['count(*)'] == 0) {
                $day = date('l', strtotime($checkDate));
                if ($day != "Sunday" && $day != "Saturday") {
                    $sqlattendance = "Select * from activityrecord where date(Date)='$checkDate' and ActivityId='$ActivityId' and NRIC='" . $row['NRIC'] . "' and $where";
                    $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                    $workingday++;
                    $resultattendance = mysql_query($sqlattendance);
                    if (mysql_num_rows($resultattendance) == 1) {
                        $row1 = mysql_fetch_array($resultattendance);
                        $performance = $row1['Performance'];
                        $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $performance);
                        $attendance++;
                        $totalPerformance += $performance;
                    } else {
                        $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, ' - ');
                    }
                    $column++;
                } else {
                    $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                }
            } else {
                $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
            }
        }
    }
    $ttlattendance += $attendance;
    if ($attendance == 0) {
        $averagePerformance = 0;
    } else {
        $averagePerformance = round($totalPerformance / $attendance, 5);
    }
    $avg = round($ttlattendance / $sno, 5);
    $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $averagePerformance);
    $column++;
    $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $attendance);
//$str.="," . $attendance . "\n";
    $styleArray = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN
            )
        )
    );
    $style2Array = array(
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THICK, PHPExcel_Style_Border::BORDER_DOUBLE
            )
        )
    );
    $objPHPExcel->getActiveSheet()->getStyle('A3:' . $column . $rowCount)->applyFromArray($styleArray);
    $sno++;
    $rowCount++;
}
$objPHPExcel->getActiveSheet()->getStyle('F4:' . $column . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Total Attendance for ' . $ActivityName . ':');
$objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':C' . $rowCount);
$objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':D' . $rowCount)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $ttlattendance);
$rowCount++;
$objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Average Attendance for ' . $ActivityName . ':');
$objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':C' . $rowCount);
$objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':D' . $rowCount)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $avg);
//creating the excel file
//MAILING
//header('Content-Disposition: attachment;filename="EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx"');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
if ($centreId == 0) {
    header('Content-Disposition: attachment;filename="EldercareActivityReport' . $startDate1 . 'to' . $endDate1 . '.xlsx"');
} else {
    include 'conn.php';
    $check1 = "Select CentreName from Centre where CentreId=$centreId";
    $resultto = mysql_query($check1);
    while ($row = mysql_fetch_array($resultto)) {
        $cname = $row['CentreName'];
    }
    header('Content-Disposition: attachment;filename="EldercareActivityReport' . $startDate1 . 'to' . $endDate1 . '-' . $cname . '.xlsx"');
}


header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
//$objWriter->save(str_replace('export_1.php', 'EldercareReport' . $startDate1 . 'to' . $endDate1 . '.xlsx', __FILE__));
//$objWriter->save('php://output');
?>

